if exists (select * from dbo.sysobjects where id = object_id(N'dbo.[usp_SelecttbOrdersPaged]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.[usp_SelecttbOrdersPaged]
GO
--Created:12/06/2011
--Author:
CREATE procedure dbo.[usp_SelecttbOrdersPaged]
@WhereClause nvarchar (4000)  ,
@OrderBy varchar (2000)  ,
@PageIndex int   ,
@PageSize int  
 as 
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize
Create Table #PageIndex
(
 [IndexId] int IDENTITY (1, 1) NOT NULL,
OrderID int
)
declare @SQL as nvarchar(4000)
SET @SQL = 'INSERT INTO #PageIndex (OrderID)'
SET @SQL = @SQL + ' SELECT'
IF @PageSize > 0
BEGIN
SET @SQL = @SQL + ' TOP ' + convert(nvarchar, @PageUpperBound)
End 
SET @SQL = @SQL + ' OrderID'
SET @SQL = @SQL + ' FROM dbo.[tbOrder]'
IF LEN(@WhereClause) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
End
IF LEN(@OrderBy) > 0
BEGIN
SET @SQL = @SQL + ' ORDER BY ' + @OrderBy
End
exec sp_executesql @SQL
select 	O.OrderID,
	O.PasengerID
 from  dbo.[tbOrder] AS O, [#PageIndex] AS PageIndex
WHERE     (PageIndex.IndexID > @PageLowerBound)  AND (O.OrderID = PageIndex.OrderID)
ORDER BY PageIndex.IndexID
SET @SQL = 'SELECT COUNT(*) as TotalRecords'
SET @SQL = @SQL + ' FROM dbo.[tbOrder]'
IF LEN(@WhereClause) > 0
BEGIN
SET @SQL = @SQL + ' WHERE ' + @WhereClause
End
exec sp_executesql @SQL
END 
GO
